package com.ctvit.sms.platform.service;

import java.sql.Timestamp;
import java.text.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Random;

import javassist.compiler.ast.CondExpr;
import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.HanyuPinyinVCharType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.id.GUIDGenerator;
import org.springframework.security.authentication.encoding.Md5PasswordEncoder;

import com.cctv.ldap.LdapPerson;
import com.cctv.ldap.LdapUtils;
import com.ctvit.framework.dao.Condition;
import com.ctvit.framework.security.LdapPasswordEncoder;
import com.ctvit.framework.security.PasswordEncodingAlgorithm;
import com.ctvit.framework.service.BaseService;
import com.ctvit.framework.web.context.ContextHolder;
import com.ctvit.sms.platform.domain.Login;
import com.ctvit.sms.platform.domain.Person;
import com.gexin.rp.sdk.base.IPushResult;
import com.gexin.rp.sdk.base.impl.SingleMessage;
import com.gexin.rp.sdk.base.impl.Target;
import com.gexin.rp.sdk.http.IGtPush;
import com.gexin.rp.sdk.template.NotificationTemplate;
import com.gexin.rp.sdk.template.TransmissionTemplate;

public class AppOnlyService extends BaseService {
	private String host = "http://sdk.open.api.igexin.com/apiex.htm";
	private String appkey = "MG0UoDc9vh6uCMs5F9fc91";
	private String appId = "tXRwidBJVV8WNqZUx4VCv8";
	private String master = "T89cErBK0r8RqEGL136Lw8";
	

	
	public Login getUserLoginInfo(String userId) {
		Condition condition=new Condition();
		condition.add("zPerno",userId);
		condition.add("zState","0");//有效的登录记录
		List<Login> logins=genericDao.getByCondition(Login.class, condition, null);
		if (logins==null||logins.size()!=1){
			return null;
		}else{
			return logins.get(0);
		}
	}
	public HashMap doUserLogin(String userId, String ip,String md5Password, String saltMd5Password,String sessionToken,String clientId) {
		HashMap hmap = new HashMap<String, String>();String sqlString="";
		LdapPasswordEncoder ldapPasswordEncoder=ContextHolder.getBean("passwordEncoder");
		if (!StringUtils.isEmpty(md5Password)){//初次登录验证
			Condition condition=new Condition();
			condition.add("zPerno",userId);
			List<Person> personList=genericDao.getByCondition(Person.class, condition, null);
			LdapUtils ldapUtils=new LdapUtils();
//			LdapPerson ldapPerson= ldapUtils.getPersonByUID(userId);
			
			//没有ldap临时代用
			LdapPerson ldapPerson=new LdapPerson();
			ldapPerson.setUserPassword(ldapPasswordEncoder.encodePassword(personList.get(0).getPassword(),null));
			
			String matchMd5Password="{MD5}"+md5Password+"==";
			boolean match=matchMd5Password.equals(ldapPerson.getUserPassword());
			if (personList!=null&&personList.size()>0&&match){//人员存在并且和ldap密码匹配则登录成功
				RandomGUID saltGuid=new RandomGUID();
				String clientPwd=ldapPasswordEncoder.encodePassword(matchMd5Password, saltGuid.toString().getBytes());
				RandomGUID sessionGuid=new RandomGUID();
				RandomGUID accessGuid=new RandomGUID();
				String saltSessionToken=ldapPasswordEncoder.encodePassword(sessionGuid.toString(),saltGuid.toString().getBytes());
				String saltAccessToken=ldapPasswordEncoder.encodePassword(accessGuid.toString(),saltGuid.toString().getBytes());
				sqlString="update t_login set z_state='1' where z_perno='"+userId+"'";
				genericDao.updateBySqlStr(sqlString);
				Login login=new Login();
				login.setZPerno(userId);
				login.setZLoginip(ip);
				Calendar calSession=Calendar.getInstance();
				calSession.add(Calendar.DATE, 30);
				Calendar calAccess=Calendar.getInstance();
				calAccess.add(Calendar.DATE, 1);
				login.setZLogintime(new Timestamp(Calendar.getInstance().getTimeInMillis()));
				login.setZState("0");
				login.setZSalt(saltGuid.toString());
				login.setZClientid(clientId);
				login.setZSessionToken(sessionGuid.toString());
				login.setZSessionExpired(new Timestamp(calSession.getTimeInMillis()));
				login.setZAccessToken(accessGuid.toString());
				login.setZAccessExpired(new Timestamp(calAccess.getTimeInMillis()));
				genericDao.insert(login);
				hmap.put("clientPwd", clientPwd);
				hmap.put("sessionToken", saltSessionToken);
				hmap.put("accessToken", saltAccessToken);
				hmap.put("username", personList.get(0).getZPerna());
				hmap.put("result", "ok");
				if (!StringUtils.isEmpty(clientId)){
					sqlString = "update t_person set z_clientID='" + clientId+ "' where z_perno='" + userId + "'";
					genericDao.updateBySqlStr(sqlString);
				}
			}else{
				hmap.put("result", "");
			}
		}else{
			Condition condition=new Condition();
			condition.add("zPerno",userId);
			condition.add("zState","0");//有效的登录记录
			List<Login> logins=genericDao.getByCondition(Login.class, condition, null);
			if (logins==null||logins.size()!=1){
				hmap.put("result", "");//没有合法的历史登录记录，
			}else {
				Login login=logins.get(0);
				LdapUtils ldapUtils=new LdapUtils();
//				LdapPerson ldapPerson= ldapUtils.getPersonByUID(userId);
				
				//没有ldap临时代用
				condition=new Condition();
				condition.add("zPerno",userId);
				List<Person> personList=genericDao.getByCondition(Person.class, condition, null);
				LdapPerson ldapPerson=new LdapPerson();
				ldapPerson.setUserPassword(ldapPasswordEncoder.encodePassword(personList.get(0).getPassword(),null));
				
				
				String saltMd5PasswordLocal=ldapPasswordEncoder.encodePassword(ldapPerson.getUserPassword(), login.getZSalt().getBytes());
				String saltSessionTokenLocal=ldapPasswordEncoder.encodePassword(login.getZSessionToken(), login.getZSalt().getBytes());
				if (saltSessionTokenLocal.equals(sessionToken)&&saltMd5PasswordLocal.equals(saltMd5Password)&&login.getZSessionExpired().after(new Timestamp(Calendar.getInstance().getTimeInMillis()))){
					RandomGUID accessGuid=new RandomGUID();
					RandomGUID sessionGuid=new RandomGUID();
					String saltSessionToken=ldapPasswordEncoder.encodePassword(sessionGuid.toString(),login.getZSalt().getBytes());
					String saltAccessToken=ldapPasswordEncoder.encodePassword(accessGuid.toString(),login.getZSalt().getBytes());
					login.setZSessionToken(sessionGuid.toString());
					login.setZAccessToken(accessGuid.toString());
					login.setModifyTimestamp(new Timestamp(Calendar.getInstance().getTimeInMillis()));
					Calendar calAccess=Calendar.getInstance();
					calAccess.add(Calendar.DATE, 1);
					login.setZAccessExpired(new Timestamp(calAccess.getTimeInMillis()));
					genericDao.update(login, new String[]{"zSessionToken","zAccessToken","modifyTimestamp","zAccessExpired"});
					hmap.put("sessionToken", saltSessionToken);
					hmap.put("accessToken", saltAccessToken);
					hmap.put("result", "ok");
				}else{
					hmap.put("result", "");//客户端保存的密码不匹配，或者session到期需要重新登录
				}
			}
		}
		return hmap;
	}
	
	/**
	 * 
	 * @ps:
	 * @param personNm人员名称
	 * @param deptNm部门名称
	 * @param pageRowNum页码第几页_默认值为1
	 * @param pageSize页面的数据量_默认值为1
	 * @return Json数据形式
	 */
	public List<Map<String, ?>> searchPerson(String personNm, String deptNm, String pageRowNum, String pageSize,String userId) {
		StringBuffer sql = new StringBuffer(
				"select p.z_perno,p.z_perna,p.z_name_pinyin_first,p.z_name_pinyin,p.z_telph,p.z_mobph,p.z_email,p.z_dept,d.z_dept_nm from t_person p left join t_dept d on p.z_dept=d.z_dept_id ");
		// 页码不为0的情况下,按条件查询,否则,查询全部
		if (!StringUtils.equals(pageSize, "0")) {
			if (personNm != null && !personNm.equals("")) { // 人员名称参数有效则据此查询
				sql.append(" where EXISTS  (SELECT * FROM  t_person  k WHERE k.z_perno='"+userId+"' AND p.z_centre=k.z_centre )  AND (z_type !='v' OR  p.z_perno='"+userId+"') and (p.z_perna like '%").append(personNm).append("%' or d.z_dept_nm like '%").append(personNm).append(
						"%' ) and p.z_mobph is not null and p.z_mobph !='' order by p.z_name_pinyin ASC");
			} else if (deptNm != null && !deptNm.equals("")) { // 部门名称参数有效则据此查询
				sql.append(" where EXISTS  (SELECT * FROM  t_person  k WHERE k.z_perno='"+userId+"' AND p.z_centre=k.z_centre )  AND (z_type !='v' OR  p.z_perno='"+userId+"') and d.z_dept_nm like '%").append(deptNm).append(
						"%' and p.z_mobph is not null and p.z_mobph !='' order by p.z_name_pinyin ASC ");
			}else{
				sql.append(" where EXISTS  (SELECT * FROM  t_person  k WHERE k.z_perno='"+userId+"' AND p.z_centre=k.z_centre )  AND (z_type !='v' OR  p.z_perno='"+userId+"')");
			}
			// 分页设置部分
			int i = Integer.parseInt(pageRowNum == null | pageRowNum.equals("") ? "1" : pageRowNum);// 需要查询的页码,默认值为1
			int pS = Integer.parseInt(pageSize == null | pageSize.equals("") ? "1" : pageSize);// 页面显示的记录数,默认值为1
			int firstRowNum = (i - 1) * pS;// 查询的起始行号
			sql.append(" limit ").append(firstRowNum).append(",").append(pS);
		}else{
			sql.append(" where EXISTS  (SELECT * FROM  t_person  k WHERE k.z_perno='"+userId+"' AND p.z_centre=k.z_centre )  AND (z_type !='v' OR  p.z_perno='"+userId+"')");
		}
		System.out.println("查询通讯录==="+sql.toString());
		List<Map<String, ?>> result = genericDao.executeSql(sql.toString());// 执行sql语句,得到结果集
		return result;
	}

	/**
	 * 
	 * @ps:
	 * @param error
	 * @param right
	 * @return
	 */
	public List<Map<String, ?>> getWorderror(String error, String right) {
		StringBuffer sql = new StringBuffer("select * from t_worderror ");
		if (error != null & !error.equals("")) {
			sql.append(" where Z_ERROR_WORD LIKE '%").append(error).append("%' limit 10 ");
		} else if (right != null & !right.equals("")) {
			sql.append(" where Z_RIGHT_WORD LIKE '%").append(right).append("%' limit 10 ");
		}
		List<Map<String, ?>> result = genericDao.executeSql(sql.toString());
		return result;
	}

	public List<Map<String, ?>> getWorderrorDoc() {
		String sql = "select * from t_worderror_doc order by f_type ASC, f_title ASC ";
		List<Map<String, ?>> result = genericDao.executeSql(sql);
		return result;
	}

	@SuppressWarnings("unchecked")
	public List<Map<String, ?>> getIcharts() {
		String sql = " select Z_RIGHT_WORD as name, count(*) as value  from t_worderror where Z_RIGHT_WORD is not null group by Z_RIGHT_WORD order by value DESC limit 10 ";
		List<Map<String, ?>> result = genericDao.executeSql(sql);
		if (result != null && result.size() > 0) {
			for (Map res : result) {
				res.put("color", randomRGB());
			}
		}
		return result;
	}

	/*
	 * 生成颜色的代码
	 */
	private String randomRGB() {
		String r, g, b, reString;
		Random random = new Random();
		r = Integer.toHexString(random.nextInt(256)).toUpperCase();
		g = Integer.toHexString(random.nextInt(256)).toUpperCase();
		b = Integer.toHexString(random.nextInt(256)).toUpperCase();
		r = r.length() == 1 ? "0" + r : r;
		g = g.length() == 1 ? "0" + g : g;
		b = b.length() == 1 ? "0" + b : b;
		reString = "#" + r + g + b;
		return reString;
	}

	/**
	 * 
	 * @ps:将数据库表t_person的z_perna列转换成全拼并更新到相应字段
	 * @return 参与操作的名字
	 */
	public List<Map<String, ?>> doChineseToPinYin() {
		String name = ""; // 保存汉字字符串
		String pinYin = ""; // 保存汉字对应的拼音字符串
		char fisrtPinYin = 0;
		StringBuffer sql = new StringBuffer("select z_perna from t_person ");
		List<Map<String, ?>> result = genericDao.executeSql(sql.toString());
		int resSize = result.size();// 保存结果集的大小
		for (int i = 0; i < resSize; i++) {
			name = result.get(i).get("z_perna").toString();// 得到名字的汉字
			pinYin = getPinYin(name);// 得到汉字的全拼
			if (pinYin != null & pinYin.length() >= 1) {
				fisrtPinYin = pinYin.toUpperCase().charAt(0);
			}
			sql = new StringBuffer("update t_person SET z_name_pinyin ='").append(pinYin).append("',z_name_pinyin_first='").append(fisrtPinYin)
					.append("' ").append("where z_perna='").append(name).append("' ");
			genericDao.updateBySqlStr(sql.toString());// 执行sql语句
		}
		return result;
	}

	/* 
	 * 获取汉字的全拼
	 */
	private String getPinYin(String str) {
		StringBuffer result = new StringBuffer();  //保存结果的字符串
		char[] ch = str.toCharArray();
		int chLen = ch.length; // 记录字符数组的长度
		String[] strs = new String[chLen];
		HanyuPinyinOutputFormat hanYuPinYin = new HanyuPinyinOutputFormat();
		hanYuPinYin.setCaseType(HanyuPinyinCaseType.LOWERCASE);
		hanYuPinYin.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
		hanYuPinYin.setVCharType(HanyuPinyinVCharType.WITH_V);
		try {
			for (int i = 0; i < chLen; i++) {
				// 判断是否为汉字字符
				if (Character.toString(ch[i]).matches("[\\u4E00-\\u9FA5]+")) {
					strs = PinyinHelper.toHanyuPinyinStringArray(ch[i], hanYuPinYin);
					result.append(strs[0]);
				}
			}
			return result.toString();
		} catch (BadHanyuPinyinOutputFormatCombination e) {
			// TODO: handle exception
		}
		return null;
	}
	
	/**
	 * 读取普通消息，会议消息
	 * @param userId
	 * @param password
	 * @param clientId
	 * @param isUpdate
	 * @return
	 */
	public List<Object> doGetMsgSum(String userId, String password,String clientId, String isUpdate) {
		List<Object> menuList = new ArrayList<Object>();
		List retList = null;
		HashMap<String, String> menuMap1 = new HashMap<String, String>();
		StringBuffer sb1=new StringBuffer();
		sb1.append("select a.z_task_id as F_ID,'' as F_IMG_URL,'' as push_title,b.Z_MSG_CONTENT as F_MSGTXT,'' as clientid,                        ");
		sb1.append("(select Z_REG_CATEGORY from t_msg_reg where Z_REG_NO=b.Z_REG_ID) as F_TYPE, ");
		sb1.append("case Z_CONTENT_TYPE when '1'  then '1' when '8' then '1' else '0' end as F_ISTYPE,                                                           ");
		sb1.append("(select f_put_userid from t_cmsg_text as c where c.f_message_pk=b.Z_FORM_ID and b.Z_CONTENT_TYPE='7') as z_PutUserId,         ");
		sb1.append("(select z_perna from t_cmsg_text as c inner join t_person as d on c.f_put_userid=d.z_perno where c.f_message_pk=b.Z_FORM_ID and b.Z_CONTENT_TYPE='7') as z_PutUserName,         ");
		sb1.append("(select z_SendDepartment from v_noticeall as c where c.Z_REQ_ID=b.Z_REQ_ID and Z_CONTENT_TYPE=b.Z_CONTENT_TYPE) as z_SendDepartment,         ");
		sb1.append("(select z_MeetingTopic from v_noticeall as c where c.Z_REQ_ID=b.Z_REQ_ID and Z_CONTENT_TYPE=b.Z_CONTENT_TYPE) as z_MeetingTopic,             ");
		sb1.append("(select z_MeetDescribe from v_noticeall as c where c.Z_REQ_ID=b.Z_REQ_ID and Z_CONTENT_TYPE=b.Z_CONTENT_TYPE) as z_MeetDescribe,             ");
		sb1.append("(select z_MeetingDate from v_noticeall as c where c.Z_REQ_ID=b.Z_REQ_ID and Z_CONTENT_TYPE=b.Z_CONTENT_TYPE) as z_MeetingDate,               ");
		sb1.append("(select z_MeetingTime from v_noticeall as c where c.Z_REQ_ID=b.Z_REQ_ID and Z_CONTENT_TYPE=b.Z_CONTENT_TYPE) as z_MeetingTime,               ");
		sb1.append("(select z_MeetingPosition from v_noticeall as c where c.Z_REQ_ID=b.Z_REQ_ID and Z_CONTENT_TYPE=b.Z_CONTENT_TYPE) as z_MeetingPosition,       ");
		sb1.append("(select z_MeetingParticipant from v_noticeall as c where c.Z_REQ_ID=b.Z_REQ_ID and Z_CONTENT_TYPE=b.Z_CONTENT_TYPE) as z_MeetingParticipant, ");
		sb1.append("(select z_MeetingContacts from v_noticeall as c where c.Z_REQ_ID=b.Z_REQ_ID and Z_CONTENT_TYPE=b.Z_CONTENT_TYPE) as z_MeetingContacts,       ");
		sb1.append("(select z_ContactInformation from v_noticeall as c where c.Z_REQ_ID=b.Z_REQ_ID and Z_CONTENT_TYPE=b.Z_CONTENT_TYPE) as z_ContactInformation, ");
		sb1.append("(func_get_noticefile(b.z_req_id)) as notice_attach, ");
		sb1.append("DATE_FORMAT(a.CREATE_TIMESTAMP,'%Y-%m-%d %H:%i:%s') as F_SUBSCRIBE_TIME                                                                      ");
		sb1.append("from t_msg_task as a inner join t_msg_req as  b on a.z_req_id=b.z_req_id                                                                     ");
		//读取所有未阅读消息，退订的除外
		sb1.append("where a.Z_RECV_USR='").append(userId).append("' and (Z_READ_TIME is null and Z_STATUS!='7')                                                        ");
		//不是老台通知系统产生的task
		sb1.append("and instr(ifnull(b.z_form_id,''),'nt')<=0                                                                                                    ");
		String sqlString=sb1.toString();
		System.out.println("查询短消息===="+sqlString);
//		a.CREATE_TIMESTAMP>(date_trunc('seconds',NOW())- interval '30 min')
		List lst = genericDao.executeSql(sqlString);
		if (lst == null)
			lst = new ArrayList();
		menuMap1.put("F_XXZX", String.valueOf(lst.size()));
		menuMap1.put("F_DZYJ", "");
		menuMap1.put("F_TZ", "");
		menuMap1.put("F_GG", "");
		menuMap1.put("F_GZRB", "");
		menuList.add(menuMap1);
		if (StringUtils.equals(isUpdate, "1")) {
			updateMsg(lst);
			retList = lst;
		} else {
			retList = menuList;
		}
		return retList;
	}

	//苹果手机推送消息时获取所有未读通知和消息
	public int doGetMessageSum(String userId) {
		// 读取普通消息，会议消息
		String sqlString = "select a.z_task_id ";
		sqlString += " from t_msg_task as a inner join `t_msg_req` as  b on a.`z_req_id`=b.`z_req_id`";
		//除去(3:短信发送成功4:短信发送失败5:已阅读7:已退订)剩下0:未发送1:推送成功2:推送失败6:勿扰时间		
		sqlString += " where a.Z_RECV_USR='" + userId+ "' and Z_STATUS not in ('3','4','5','7')";
		sqlString += " and instr(b.z_form_id,'nt')<=0";// 不是老台通知系统产生的task
		System.out.println("查询消息数量==="+sqlString);
		List lst = genericDao.executeSql(sqlString);
		
		// 读取老台系统通知
		sqlString = "select a.z_task_id as F_ID,b.Z_MSG_CONTENT as F_MSGTXT,'' as clientid,'' as F_TYPE,'2' as F_ISTYPE,"
				+ "DATE_FORMAT(a.CREATE_TIMESTAMP,'%Y-%m-%d %H:%i:%s') as F_SUBSCRIBE_TIME,c.name as sendname,c.SENDID,c.SENDDEPTNAME,NOTICETITLE,"
				+ "NOTICEBRIEF,MADETIME,ENDTIME ";
		sqlString += " from t_msg_task as a inner join `t_msg_req` as  b on a.`z_req_id`=b.`z_req_id`";
		sqlString += " left join v_notice as c on b.Z_FORM_ID=concat('nt',c.NOTICEID)";
		sqlString += " where a.Z_RECV_USR='" + userId+ "' and a.Z_STATUS not in ('3','4','5','7')";
		sqlString += " and instr(b.z_form_id,'nt')>0";// 老台通知系统产生的task
		System.out.println("查询通知数量==="+sqlString);
		List lstOth = genericDao.executeSql(sqlString);
		
		int sum = 0;
		if(lst!=null&&lst.size()>0){
			sum = sum + lst.size();
		}
		if(lstOth!=null&&lstOth.size()>0){
			sum = sum + lstOth.size();
		}
		
		return sum;
	}
	
	
	
	/**
	 * 
	 * @ps:
	 * @param userId
	 * @return
	 */
	public List<Object> doGetMessage(String userId, String password,
			String clientId, String isUpdate) {
		List<Object> menuList = new ArrayList<Object>();
		Map<String, String> menuMap = new HashMap<String, String>();
		// 读取普通消息，会议消息
		String sqlString = "select a.z_task_id as F_ID,'' as F_IMG_URL,'' as push_title,b.Z_MSG_CONTENT as F_MSGTXT,'' as clientid,'' as F_TYPE,'0' as F_ISTYPE,"
				+ "DATE_FORMAT(a.CREATE_TIMESTAMP,'%Y-%m-%d %H:%i:%s') as F_SUBSCRIBE_TIME";
		sqlString += " from t_msg_task as a inner join `t_msg_req` as  b on a.`z_req_id`=b.`z_req_id`";
		//除去(3:短信发送成功4:短信发送失败5:已阅读7:已退订)剩下0:未发送1:推送成功2:推送失败6:勿扰时间		
//		sqlString += " where a.Z_RECV_USR='" + userId+ "' and Z_STATUS not in ('3','4','5','7') AND Z_READ_TIME IS NULL  ";
		sqlString += " where a.Z_RECV_USR='" + userId+ "' and Z_PUSH_TIME is not null AND Z_READ_TIME IS NULL  ";
		sqlString += " and instr(b.z_form_id,'nt')<=0";// 不是老台通知系统产生的task
		System.out.println("读取普通消息=========="+sqlString);
		List lst = genericDao.executeSql(sqlString);
		// 读取老台系统通知
		sqlString = "select a.z_task_id as F_ID,b.Z_MSG_CONTENT as F_MSGTXT,'' as clientid,'' as F_TYPE,'2' as F_ISTYPE,"
				+ "DATE_FORMAT(a.CREATE_TIMESTAMP,'%Y-%m-%d %H:%i:%s') as F_SUBSCRIBE_TIME,c.name as sendname,c.SENDID,c.SENDDEPTNAME,NOTICETITLE,"
				+ "NOTICEBRIEF,MADETIME,ENDTIME ";
		sqlString += " from t_msg_task as a inner join `t_msg_req` as  b on a.`z_req_id`=b.`z_req_id`";
		sqlString += " left join v_notice as c on b.Z_FORM_ID=concat('nt',c.NOTICEID)";
//		sqlString += " where a.Z_RECV_USR='" + userId+ "' and a.Z_STATUS not in ('3','4','5','7')";
		sqlString += " where a.Z_RECV_USR='" + userId+ "' and Z_PUSH_TIME is not null AND Z_READ_TIME IS NULL  ";
		sqlString += " and instr(b.z_form_id,'nt')>0";// 老台通知系统产生的task
		System.out.println("老台系统通知=========="+sqlString);
		List lstOth = genericDao.executeSql(sqlString);
		if (lst == null) {
			lst = new ArrayList();
		} else if (lstOth == null) {
			lstOth = new ArrayList();
		}
		menuMap.put("F_XXZX", String.valueOf(lst.size()));
		menuMap.put("F_DZYJ", "");
		menuMap.put("F_TZ", String.valueOf(lstOth.size()));
		menuMap.put("F_GG", "");
		menuMap.put("F_GZRB", "");
		menuList.add(menuMap);
		return menuList;
	}
	
	/**
	 * 读取老台系统通知
	 * @param userId
	 * @param password
	 * @param clientId
	 * @param isUpdate
	 * @return
	 */
	public List<Object> doGetNoticeSum(String userId, String password,
			String clientId, String isUpdate) {
		List<Object> menuList = new ArrayList<Object>();
		List retList = null;
		HashMap<String, String> menuMap1 = new HashMap<String, String>();
		String sqlString = "select a.z_task_id as F_ID,b.Z_MSG_CONTENT as F_MSGTXT,'' as clientid,'' as F_TYPE,'2' as F_ISTYPE,"
				+ "DATE_FORMAT(a.CREATE_TIMESTAMP,'%Y-%m-%d %H:%i:%s') as F_SUBSCRIBE_TIME,c.name as sendname,c.SENDID,c.SENDDEPTNAME,NOTICETITLE,"
				+ "NOTICEBRIEF,MADETIME,ENDTIME,case b.z_reg_id when 'EF08007' then func_get_noticefile(b.z_req_id) end as notice_attach";
		sqlString += " from t_msg_task as a inner join `t_msg_req` as  b on a.`z_req_id`=b.`z_req_id`";
		sqlString+=" left join v_notice as c on b.Z_FORM_ID=concat('nt',c.NOTICEID)";
		sqlString += " where a.Z_RECV_USR='" + userId+ "' and a.Z_STATUS not in ('3','4','5','7')";
		sqlString +=" and instr(b.z_form_id,'nt')>0";//老台通知系统产生的task
		List lst = genericDao.executeSql(sqlString);
		if (lst == null)
			lst = new ArrayList();
		menuMap1.put("F_XXZX", "");
		menuMap1.put("F_DZYJ", "");
		menuMap1.put("F_TZ", String.valueOf(lst.size()));
		menuMap1.put("F_GG", "");
		menuMap1.put("F_GZRB", "");
		menuList.add(menuMap1);
		if (StringUtils.equals(isUpdate, "1")) {
			updateMsg(lst);
			retList = lst;
		} else {
			retList = menuList;
		}
		return retList;
	}
	/**
	 * 
	 * @ps:
	 * @return
	 */
	public List<Map<String, ?>> getMenuList(String funType) {
		List<Map<String, ?>> result = new ArrayList<Map<String, ?>>();// 返回的结果集合
		String sqlO = "select f_name,f_icon,f_url from t_carousel_pic";// 轮播图片的sql语句
		String sqlT = "select f_code,f_name,f_icon,f_url,f_haveheader from t_function_list where f_state = "
				+ funType + " order by f_state ASC";// sql语句,确保state列的值为0
		List<Map<String, ?>> resultO = genericDao.executeSql(sqlO);
		List<Map<String, ?>> resultT = genericDao.executeSql(sqlT);
		if (resultO != null & resultT != null) {
			boolean booO = result.addAll(resultO);
			boolean booT = result.addAll(resultT);
			if (booT & booO) {
				return result;// 返回结果集
			}
		}
		return null;
	}

	/**
	 * 
	 * @ps:获取服务器信息
	 * @return
	 */
	public List<Map<String, ?>> getServicePhoneList() {
		String sql = " select f_dept_name AS F_DEPT_NAME,f_phone_num AS F_PHONE_NUM,f_worktime AS F_WORKTIME from t_service_phone order by f_order ASC ";
		List<Map<String, ?>> result = genericDao.executeSql(sql);
		return result;
	}

	/**
	 * 
	 * @ps:获取节目单子表的部分内容,
	 * @return
	 */
	public List<Map<String, ?>> getProList(String playDate, String chanlCd) {
		StringBuffer sql = new StringBuffer(
				"select Z_IS_AD,Z_PLAY_TIME,Z_PROCODE,Z_PGNAM,Z_SUBJ_TIME,Z_IN_FORM,Z_PLAY_FORM,Z_LIMIT_AD,Z_AD_TIME from zcdm_t_proglist where Z_PLAY_DATE = '");
		sql.append(playDate).append("' and Z_CHANL_CD = '").append(chanlCd).append("' order by Z_PLAY_TIME ASC");
		List<Map<String, ?>> result = genericDao.executeSql(sql.toString());
		return result;
	}

	/**
	 * 
	 * @ps:得到频道信息按频道代码排序
	 * @return
	 */
	public List<Map<String, ?>> getChannlList() {
		String sql = "select z_chanl_cd,z_chanl_name,z_icon,concat(z_chanl_cd,'-',z_chanl_name) as title from zcdm_t_chanl where Z_CHANL_TYPE = '0' order by Z_CHANL_CD ";
		List<Map<String, ?>> result = genericDao.executeSql(sql);
		return result;
	}


	/**
	 * 
	 * @ps:获取表t_tel_way
	 * @return
	 */
	public List<Map<String, ?>> getContactList() {
		String sql = "select * from t_tel_way order by f_order ASC ";
		List<Map<String, ?>> result = genericDao.executeSql(sql);
		return result;
	}

	public List<Map<String, ?>> getGongZuoRB() {
		String sql = "select * from t_gongzuoribao ";
		List<Map<String, ?>> result = genericDao.executeSql(sql);
		return result;
	}

	/**
	 * 
	 * @ps:节目单内容
	 * @param jmdm节目代码
	 * @param jmmc节目名称
	 * @param lmdm栏目代码
	 * @return
	 */
	public List<Map<String, ?>> searchJmdm(String jmdm, String jmmc, String lmdm, String userid) {
		StringBuffer sql = new StringBuffer(
				"select z_procode,z_aud_ver,z_cap_ver,z_subj_cd,z_pgnam,z_time_estm,z_prodstat from zppm_t_proginfo where z_procode not in (select f_procode from t_myconcern where f_user = '");
		sql.append(userid).append("' and f_state='0' ) ");
		
		//20160427曹召启添加，只查询未入库节目
		sql.append(" AND (z_Prodstat IS NULL OR ");
		sql.append(" (z_Prodstat IS NOT NULL AND z_Prodstat != '6' AND ");
		sql.append("  z_Prodstat != '9')) ");
		
		if (jmdm != null & !jmdm.equals("")) {
			sql.append(" and z_procode LIKE '%").append(jmdm).append("%' ");
		} else {
			if (jmmc != null & !jmmc.equals("")) {
				sql.append(" and z_pgnam LIKE '%").append(jmmc).append("%' ");
			} else {
				if (lmdm != null & !lmdm.equals("")) {
					sql.append(" and z_subj_cd = '").append(lmdm).append("' ");
				}
			}
		}
		sql.append(" order by z_procode desc LIMIT 100");
		List<Map<String, ?>> result = genericDao.executeSql(sql.toString());
		return result;
	}

	public List<Map<String, ?>> getSubj(String subjCode, String channCode, String subjName) {
		StringBuffer sql = new StringBuffer(
				"select s.Z_SUBJ_CD,s.Z_SUBJ_NAME,s.Z_SUBJ_LENGTH,s.Z_CHANL_CD,c.Z_CHANL_NAME,concat(s.Z_SUBJ_CD,'-',s.Z_SUBJ_NAME) as title from zcdm_t_subj s left join zcdm_t_chanl c on s.Z_CHANL_CD=c.Z_CHANL_CD ");
		if (subjCode != null & !subjCode.equals("")) {
			sql.append(" where s.Z_SUBJ_CD ='").append(subjCode).append("' ");
		} else {
			if (channCode != null & !channCode.equals("")) {
				sql.append(" where s.Z_CHANL_CD ='").append(channCode).append("' ");
			} else {
				if (subjName != null & !subjName.equals("")) {
					sql.append(" where s.Z_SUBJ_NAME LIKE '%").append(subjName).append("%' ");
				}
			}
		}
		sql.append(" and s.Z_SUBJ_STATUS='0' and s.Z_SUBJ_TYPE<>'2' LIMIT 50 ");
		List<Map<String, ?>> result = genericDao.executeSql(sql.toString());
		return result;
	}

	/**
	 * 
	 * @ps:对数据库表t_MyConcern进行操作
	 * @return
	 */
	public List<Map<String, ?>> updateMyConcern(String f_procode, String f_user, String f_opera, String f_cap_ver, String f_aud_ver) {
		//得到当前时间
		Calendar ca = Calendar.getInstance();
		Date d = ca.getTime();
		SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//24小时制 
		String curentdate = fmt.format(d);
		
		
		StringBuffer sb = new StringBuffer();
		sb.append(" update ");
		sb.append(" t_myconcern ");
		sb.append(" set f_state='1', "); //curentdate
		sb.append(" modify_timestamp='"+curentdate+"' ");
		sb.append(" where ");
		sb.append(" f_procode = '"+f_procode+"' ");
		sb.append(" and f_user = '"+f_user+"' ");
		sb.append(" and f_cap_ver = '"+f_cap_ver+"' ");
		sb.append(" and f_aud_ver = '"+f_aud_ver+"'");
		System.out.println("取消关注===="+sb.toString());
		int num = genericDao.updateBySqlStr(sb.toString());
		List list = new ArrayList();
		list.add(num);
		
		
		
//		StringBuffer sql = new StringBuffer(" select f_id from t_myconcern ");
//		sql.append(" where f_procode= '").append(f_procode).append("' and f_user='").append(f_user).append("' and f_cap_ver='").append(f_cap_ver)
//				.append("' and f_aud_ver='").append(f_aud_ver).append("'");
//		System.out.println("关注---1=="+sql.toString());
//		List<Map<String, ?>> result = genericDao.executeSql(sql.toString());
//		if (result.size() == 0) {
//			// 查询结果为空的话,根据条件插入一条新的数据
//			sql = new StringBuffer("insert into t_myconcern(f_procode,f_user,f_state,f_cap_ver,f_aud_ver,create_timestamp) values('");
//			sql.append(f_procode).append("','").append(f_user).append("','").append(0).append("','").append(f_cap_ver).append("','")
//					.append(f_aud_ver).append("',now())");
//			
//			System.out.println("关注---2=="+sql.toString());
//			genericDao.updateBySqlStr(sql.toString());
//		} else {
//			// 查询结果不为空,t_opera值为1,则删除相应数据
//			if (f_opera.equals("1")) {
//				int id = Integer.parseInt(result.get(0).get("f_id").toString());// 记录结果集中的id值
////				sql = new StringBuffer("delete from t_myconcern where f_id = ").append(id);
//				sql = new StringBuffer("update t_myconcern set f_state='1' where f_id = ").append(id);//20160426曹召启将取消修改为更改状态
//				System.out.println("关注---3=="+sql.toString());
//				genericDao.updateBySqlStr(sql.toString());
//			}
//		}
		return list;
	}

	public List<Map<String, ?>> updateMyConcernAdd(String f_procode, String f_user, String f_opera, String f_cap_ver, String f_aud_ver) {
		//得到当前时间
		Calendar ca = Calendar.getInstance();
		Date d = ca.getTime();
		SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//24小时制 
		String curentdate = fmt.format(d);
		
		StringBuffer sql = new StringBuffer(" select f_id from t_myconcern ");
		sql.append(" where f_procode= '").append(f_procode).append("' and f_user='").append(f_user).append("' and f_cap_ver='").append(f_cap_ver)
				.append("' and f_aud_ver='").append(f_aud_ver).append("'");
		System.out.println("关注---1=="+sql.toString());
		List<Map<String, ?>> result = genericDao.executeSql(sql.toString());
		System.out.println("result.size()===="+result.size());
		if(result!=null&&result.size()>0){
			// 查询结果不为空,修改相应状态
//			if (f_opera.equals("1")) {
				int id = Integer.parseInt(result.get(0).get("f_id").toString());// 记录结果集中的id值
//				sql = new StringBuffer("delete from t_myconcern where f_id = ").append(id);
				sql = new StringBuffer("update t_myconcern set f_state='0',modify_timestamp='"+curentdate+"' where f_id = ").append(id);//20160426曹召启将取消修改为更改状态
				System.out.println("关注---3=="+sql.toString());
				genericDao.updateBySqlStr(sql.toString());
//			}
		}else{
			// 查询结果为空的话,根据条件插入一条新的数据
			sql = new StringBuffer("insert into t_myconcern(f_procode,f_user,f_state,f_cap_ver,f_aud_ver,create_timestamp) values('");
			sql.append(f_procode).append("','").append(f_user).append("','").append(0).append("','").append(f_cap_ver).append("','")
					.append(f_aud_ver).append("',now())");
			
			System.out.println("关注---2=="+sql.toString());
			genericDao.updateBySqlStr(sql.toString());
		}

		return result;
	}
	
	
	
	public List<Map<String, ?>> getMyConfig(String userid) {
		StringBuffer sql = new StringBuffer(
				"select f_receive_time_from,f_receive_time_to,f_subs_role,f_receive_channel,'' as f_user_subs_follow_pk,'' as f_follow_type,''  as f_follow_cd,'' as f_lasttime from t_user_subs_config  where f_user_id = '");
		sql.append(userid).append("' ");
		List<Map<String, ?>> result = genericDao.executeSql(sql.toString());
		sql = new StringBuffer(
				"select '' as f_receive_time_from,'' as f_receive_time_to,'' as f_subs_role,'' as f_receive_channel,f_user_subs_follow_pk,f_follow_type,f_follow_cd,");
		sql.append(" CASE f_follow_type ").append(
				" when 'CHA' then (SELECT z_chanl_name from zcdm_t_chanl where z_chanl_cd=t_user_subs_follow.f_follow_cd)").append(
				" when 'SUB' then (SELECT z_SUBJ_name from zcdm_t_SUBJ where z_SUBJ_cd=t_user_subs_follow.f_follow_cd)").append(
				" when 'SYS' then (SELECT z_SYS_name from t_recv_system where z_sys_id=t_user_subs_follow.f_follow_cd)").append(
				"END as f_follow_name  from t_user_subs_follow where f_status = '0' and f_user_id = '").append(userid).append(
				"' order by f_follow_type asc,f_follow_cd asc ");
		List<Map<String, ?>> resultOth = new ArrayList<Map<String, ?>>();
		resultOth = genericDao.executeSql(sql.toString());
		result.addAll(resultOth);
		return result;
	}

	public List<Map<String, ?>> updateMyConfig(String userId, String timeFrom, String timeTo, String channel, String CHA, String SUB, String SYS) {
		StringBuffer sql = new StringBuffer("select f_user_id from t_user_subs_config where f_user_id='");
		sql.append(userId).append("' ");
		List<Map<String, ?>> result = genericDao.executeSql(sql.toString());
		if (result.size() == 0) {
			// 查询结果为空,执行插入操作
			sql.append("insert into t_user_subs_config(f_user_id,f_receive_time_from,f_receive_time_to,f_receive_channel,CREATE_TIMESTAMP) values('").append(userId)
					.append("','").append(timeFrom).append("','").append(timeTo).append("','").append(channel).append("',now())");
			genericDao.updateBySqlStr(sql.toString()); // 执行sql语句
		} else {
			// 不为空,执行更新操作
			sql.append("update t_user_subs_config SET MODIFY_TIMESTAMP = now(), f_receive_time_from ='").append(timeFrom).append("',f_receive_time_to='").append(timeTo)
					.append("',f_receive_channel='").append(channel).append("' where f_user_id='").append(userId).append("' ");
			genericDao.updateBySqlStr(sql.toString());// 执行sql语句
		}
		conversion(userId, CHA, "CHA");
		conversion(userId, SUB, "SUB");
		conversion(userId, SYS, "SYS");
		return result;
	}

	/**
	 * 
	 * @ps:根据userId和表t_user_subs_follow中的列f_follow_type所
	 * @ps:对应的字符串的处理结果作相应的操作
	 * @param userId
	 * @param type
	 */
	private void conversion(String userId, String type, String typeName) {
		String[] cha = type.split("\\*");
		String value = "";// 保存分割字符串后的单个字符串
		StringBuffer sql = null;
		for (int i = 0; i < cha.length; i++) {
			value = cha[i];
			boolean booI = value.indexOf("I") == 0;
			boolean booD = value.indexOf("D") == 0;
			if (booI) {
				// 以I开头,执行查询/插入操作
				sql = new StringBuffer("select f_user_subs_follow_pk from t_user_subs_follow where f_user_id='");
				sql.append(userId).append("' and f_follow_type='").append(typeName).append("' and f_follow_cd='").append(value.substring(1)).append(
						"' and f_status ='1' ");
				List<Map<String, ?>> result = genericDao.executeSql(sql.toString());
				if (result.size() == 0) {
					// 数据库不存在该条记录,执行插入操作
					sql = new StringBuffer("insert into t_user_subs_follow(f_user_id,f_follow_type,f_follow_cd,f_status) values('");
					sql.append(userId).append("','").append(typeName).append("','").append(value.substring(1)).append("','0') ");
					int sum = genericDao.updateBySqlStr(sql.toString());// 执行插入语句
					// System.out.println("插入的数据条数为: " + sum);
				}
			} else if (booD) {
				// 以D开头,执行删除操作
				sql = new StringBuffer("update t_user_subs_follow SET f_status = '1' where f_user_id='");
				sql.append(userId).append("' and f_follow_type='").append(typeName).append("' and f_follow_cd='").append(value.substring(1)).append(
						"' ");
				int sum = genericDao.updateBySqlStr(sql.toString());// 执行删除语句
				// System.out.println("删除的数据条数为: " + sum);
			}
		}
	}

	/**
	 * 
	 * @ps:
	 * @param flUserid用户id
	 * @param flType数据库列的类型
	 * @param flValue对应的值
	 * @param flState需要执行的操作类型
	 * @return
	 */
	public List<Map<String, ?>> updateMyConfigOth(String flUserid, String flType, String flValue, String flState) {
		List<Map<String, ?>> result = null;// 返回的结果集合
		StringBuffer sql = null;
		if (searchStr(flType)) {
			// 为真,根据state的值执行相应操作
			if (flState.trim().equals("I")) {
				sql = new StringBuffer("INSERT INTO t_user_subs_follow (f_user_id,f_follow_type,f_follow_cd,f_status,CREATE_TIMESTAMP) VALUES ('");
				sql.append(flUserid).append("','").append(flType).append("','").append(flValue).append("','0',now()) ");
				genericDao.updateBySqlStr(sql.toString());// 执行插入语句
			} else if (flState.trim().equals("D")) {
				sql = new StringBuffer("update t_user_subs_follow set f_status = '1',MODIFY_TIMESTAMP = now() where f_user_id =");
				sql.append(flUserid).append(" and f_follow_type= '").append(flType).append("' and f_follow_cd= '").append(flValue).append("' ");
				genericDao.updateBySqlStr(sql.toString());// 执行删除语句
			}
		} else {
			// 为假,则先根据id查询表中是否存在相应数据
			String colName = getColName(flType);// 得到对应的列名
			if (colName.equals("")) { // 无效数据则返回null
				return null;
			}
			sql = new StringBuffer("select f_user_id from t_user_subs_config where f_user_id='").append(flUserid).append("' ");
			result = genericDao.executeSql(sql.toString());// 执行查询语句
			if (result.size() == 0) {
				// 查询结果为空, 则执行插入操作
				sql = new StringBuffer("insert into t_user_subs_config(f_user_id,");
				sql.append(colName).append(",CREATE_TIMESTAMP) VALUES ('").append(flUserid).append("','").append(flValue).append("',now())");
				genericDao.updateBySqlStr(sql.toString());// 执行插入语句
			} else {
				// 执行更新操作
				sql = new StringBuffer("update t_user_subs_config set MODIFY_TIMESTAMP = now(),").append(colName).append("='");
				sql.append(flValue).append("' where f_user_id ='").append(flUserid).append("' ");
				genericDao.updateBySqlStr(sql.toString());// 执行更新语句
			}
		}
		return result;
	}

	/*
	 * 根据flType的值分类
	 */
	private boolean searchStr(String str) {
		String[] arr = { "CHA", "SUB", "SYS" };
		for (int i = 0; i < arr.length; i++) {
			if (arr[i].equals(str.trim()))
				return true;
		}
		return false;
	}

	/*
	 * 根据参数名称匹配对应的列名
	 */
	private String getColName(String type) {
		String value = type.trim(); 
		if (value.equals("JSQD")) {
			return "f_receive_channel";
		} else if (value.equals("JSSJ")) {
			return "f_receive_time_to";
		} else if (value.equals("KSSJ")) {
			return "f_receive_time_from";
		}
		return "";
	}

	/**
	 * 
	 * @ps:
	 * @return
	 */
	public List<Map<String, ?>> getSys() {
		String sql = "select z_sys_id,z_sys_name, concat(z_sys_id,'-',z_sys_name) as title from t_recv_system ORDER  BY z_sys_id ";
		List<Map<String, ?>> result = genericDao.executeSql(sql);
		return result;
	}

	public List<Map<String, ?>> getSubscription(String userid) {
		StringBuffer sql = new StringBuffer(
				"select * FROM(select a.f_msg_category,a.f_msg_category_name,'' as f_msg_sub_cat,'' as f_msg_sub_cat_name,'1' as f_parent,");
		sql.append(" (SELECT f_is_able from t_user_subs where f_user_id='").append(userid).append(
				"' and f_msg_sub_cat like CONCAT(a.f_msg_category,'%') LIMIT 1) as f_is_open from t_msg_category as a ").append(
				" WHERE a.f_is_use='0' and a.f_status = '1' UNION SELECT b.f_msg_category,'',b.f_msg_sub_cat,b.f_msg_sub_cat_name,'0' as f_parent, ").append(
				" (select f_is_open from t_user_subs where f_user_id='").append(userid).append("' and f_msg_sub_cat=b.f_msg_sub_cat) as f_is_open ")
				.append(" from t_msg_sub_cat as b ").append(" WHERE b.f_is_use='0' and b.f_status = '1') ").append(" as aa ORDER BY aa.f_msg_category,aa.f_msg_sub_cat ");
		List<Map<String, ?>> result = genericDao.executeSql(sql.toString());
		return result;
	}

	public List<Map<String, ?>> getProgState(String userid) {
		StringBuffer sql = new StringBuffer("SELECT c.f_procode,f_cap_ver,f_aud_ver,d.z_prodstat,d.z_pgnam,a.Z_PLAY_DATE,");
		sql.append(" a.Z_CHANL_CD,func_get_chanl_name(a.Z_CHANL_CD)as Z_CHANL_NAME,a.Z_PLAY_TIME, ").append(
				" (select Z_PRO_ITEM_STA from zcdm_t_monitor as b  ").append(
				" where b.z_system_id in ('ES01','PPS') and b.z_pro_item_Cd=a.Z_PRO_ITEM_CD) as esStatus, ").append(
				" (select Z_PRO_ITEM_STA from zcdm_t_monitor as b  ").append(
				" where b.z_system_id='EB02' and b.z_pro_item_Cd=a.Z_PRO_ITEM_CD) as ebStatus ").append(" from t_myconcern as c  ").append(
				" LEFT JOIN zcdm_t_proglist as a  on c.f_procode=a.z_procode and c.f_aud_ver=a.Z_AUD_VER and c.f_cap_ver=a.Z_CAP_VER and a.Z_PLAY_DATE>=NOW() ").append(
				" LEFT JOIN zppm_t_proginfo as d on c.f_procode=d.z_procode and c.f_aud_ver=d.Z_AUD_VER and c.f_cap_ver=d.Z_CAP_VER ").append(
				"  ").append(" where f_user='").append(userid).append("' and c.f_state<>1 ").append(
				" ORDER BY c.f_create_time desc,Z_PLAY_DATE,Z_PLAY_TIME,a.Z_CHANL_CD ");
		System.out.println("查询我的关注===="+sql.toString());
		List<Map<String, ?>> result = genericDao.executeSql(sql.toString());
		return result;
	}

	public List<Map<String, ?>> getMeeting() {
		String sql = " select * from t_meeting_msg ";
		List<Map<String, ?>> result = genericDao.executeSql(sql);
		return result;
	}
	
	public List<Map<String, ?>> getWorderrorBianXi(String words) {
		StringBuffer sql = new StringBuffer("select * from t_worderror_bianxi ");
		sql.append("where (f_right like '%").append(words).append("%' or f_error like '%").append(words).append("%') ")
				.append("order by f_right_spell ");
		List<Map<String, ?>> result = genericDao.executeSql(sql.toString());
		return result;
	}

	public List<Map<String, ?>> updateWorrderBianxiPinyin() {
		List<Map<String, ?>> list = genericDao
				.executeSql("select f_right,f_error from t_worderror_bianxi where f_right_spell is null or f_error_spell is null ");
		StringBuffer sql = null;// 执行你更新的sql语句
		int size = list.size();
		for (int i = 0; i < size; i++) {
			String right = list.get(i).get("f_right").toString();
			String error = list.get(i).get("f_error").toString();
			sql = new StringBuffer("update t_worderror_bianxi set f_right_spell='");
			sql.append(getPinYin(right)).append("',f_error_spell='").append(getPinYin(error)).append("' ").append("where f_right='").append(right)
					.append("' and f_error='").append(error).append("' ");
			genericDao.updateBySqlStr(sql.toString());
		}
		return list;
	}

	public int updateMsg(List<HashMap> msgList) {
		int ret = 0;
		String sqlString = "";StringBuffer taskIdsBuffer=new StringBuffer();
		taskIdsBuffer.append("(");
		for (HashMap map : msgList) {
			taskIdsBuffer.append(map.get("F_ID")).append(",");
		}
		taskIdsBuffer.append("-1)");
		//已阅读
		sqlString = "update t_msg_task set Z_STATUS='5', Z_READ_TIME=now(), MODIFY_TIMESTAMP=now() where z_task_id in "+taskIdsBuffer.toString();
		genericDao.updateBySqlStr(sqlString);
		return ret;
	}

	public int saveMsg(String userid, String content, String title) {
		String sqlString = "insert into push_message(userid,push_content,push_title,is_read,is_push,createtime) values('"
				+ userid + "','" + content + "','" + title + "','0','0',now())";
		int ret = genericDao.updateBySqlStr(sqlString);
		try {
			pushMsg(userid);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ret;
	}

	public void pushMsg(String userId) throws Exception {
		IGtPush push = new IGtPush(host, appkey, master);
		push.connect();
		String sqlString = "select a.id,a.push_title,a.push_content,clientid";
		sqlString += " from push_message as a inner join `user_information` as  b on a.`userid`=b.`userID`";
		sqlString += " where a.userid='" + userId + "' and is_push='0'";
		List lsList = genericDao.executeSql(sqlString);
		if (lsList != null && lsList.size() > 0) {
			NotificationTemplate template = null;
			TransmissionTemplate transmissionTemplate = null;
			HashMap hMap = null;
			SingleMessage message = new SingleMessage();
			Target target1 = null;
			for (Iterator iterator = lsList.iterator(); iterator.hasNext();) {
				hMap = (HashMap) iterator.next();
				template = NotificationTemplateDemo(String.valueOf(hMap
						.get("push_content")), String.valueOf(hMap
						.get("push_title")));
				if (String.valueOf(hMap.get("push_content")).startsWith("TS")) {
					transmissionTemplate = transmissionTemplateDemo(String
							.valueOf(hMap.get("push_content")), String
							.valueOf(hMap.get("push_title")));
				}
				message = new SingleMessage();
				message.setOffline(true);
				message.setOfflineExpireTime(1 * 1000 * 3600);
				message.setData(template);
				if (String.valueOf(hMap.get("push_content")).startsWith("TS")) {
					message.setData(transmissionTemplate);
				}
				target1 = new Target();
				target1.setAppId(appId);
				target1.setClientId(String.valueOf(hMap.get("clientid")));
				IPushResult ret = push.pushMessageToSingle(message, target1);
				sqlString = "update push_message set is_push='1' where id='"
						+ String.valueOf(hMap.get("id")) + "'";
				genericDao.updateBySqlStr(sqlString);
			}
		}
		// TransmissionTemplate template = TransmissionTemplateDemo();
		// LinkTemplate template = linkTemplateDemo();
		// NotificationTemplate template = NotificationTemplateDemo();
		// NotyPopLoadTemplate template =NotyPopLoadTemplateDemo();

		// SingleMessage message = new SingleMessage();
		// message.setOffline(true);
		// message.setOfflineExpireTime(1 * 1000 * 3600);
		// message.setData(template);
		// message.setPushNetWorkType(1); //根据WIFI推??设置

		// List<Target> targets = new ArrayList<Target>();
		// Target target1 = new Target();
		// target1.setAppId(appId);
		// target1.setClientId(CID);
		// target1.setAlias(Alias); //根据别名推??设置，CID与Alias可二选一进行推??

		// IPushResult ret = push.pushMessageToSingle(message, target1);
	}

	public NotificationTemplate NotificationTemplateDemo(String content,
			String title) throws Exception {
		NotificationTemplate template = new NotificationTemplate();
		template.setAppId(appId);
		template.setAppkey(appkey);
		template.setTitle(content);
		template.setText(title);
		template.setLogo("icon.png");
		// template.setLogoUrl("");
		// template.setIsRing(true);
		// template.setIsVibrate(true);
		// template.setIsClearable(true);
		template.setTransmissionType(1);
		template.setTransmissionContent("dddd");
		// template.setPushInfo("actionLocKey", 2, "message", "sound",
		// "payload", "locKey", "locArgs", "launchImage");
		return template;
	}

	public TransmissionTemplate transmissionTemplateDemo(String content,
			String title) throws Exception {
		TransmissionTemplate template = new TransmissionTemplate();
		template.setAppId(appId);
		template.setAppkey(appkey);
		// 透传消息设置??为强制启动应用，客户端接收到消息后就会立即启动应用；2为等待应用启??
		template.setTransmissionType(1);
		template.setTransmissionContent(content);
		template.setPushInfo(content, 3, "message", "sound", "payload", title,
				"locArgs", "launchImage");

		// 设置定时展示时间
		// template.setDuration("2015-01-16 11:40:00", "2015-01-16 12:24:00");
		return template;
	}

	
	//保存工作日历提醒信息
	public List saveRemind(String title,String startTime,String endTime,String note,String type,String userId,String remind_id) {


		
		List list = new ArrayList();
		StringBuffer sb = new StringBuffer();
		
		String remindTimt = startTime;
		SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//24小时制 
		try {			
			if(remindTimt!=null&&!remindTimt.equals("")){
				Date date = fmt.parse(remindTimt);
				Date dateTemp = null;
				//计算提醒时间
				if(type!=null&&!type.equals("")){
					if(type.equals("1")){
						dateTemp = new Date(date.getTime() - 3*600000); //30分钟前的时间
					}else if(type.equals("2")){
						dateTemp = new Date(date.getTime() - 6*600000); //一小时前的时间
					}else if(type.equals("3")){
						dateTemp = new Date(date.getTime() - 12*600000); //二小时前的时间
					}else if(type.equals("4")){
						dateTemp = new Date(date.getTime() - 36*600000); //6小时前的时间
					}
					if(dateTemp!=null){
						remindTimt = fmt.format(dateTemp);	
					}
				}	
			}
		} catch (ParseException e) {
			e.printStackTrace();
		}

		//得到当前时间
		Calendar ca = Calendar.getInstance();
		Date d = ca.getTime();
		String curentdate = fmt.format(d);
		System.out.println("当前时间======="+curentdate);
		

		//修改
		if(remind_id!=null&&!remind_id.equals("null")&&!remind_id.equals("")){
			sb.setLength(0);
			sb.append("update  t_calendar_warn set ");
			sb.append("TITLE= '"+title+"',");
			sb.append("START_TIME = '"+startTime+"',");
			sb.append("END_TIME = '"+endTime+"',");
			sb.append("NOTE ='"+note+"',");
			sb.append("TYPE = '"+type+"',");
			sb.append("USER_ID = '"+userId+"',");
			sb.append("REMIND_TIME = '"+remindTimt+"',");
			sb.append("STATUS = '0',");
			sb.append("MODIFY_TIMESTAMP = '"+curentdate+"' ");//修改时间
			sb.append(" where id = '"+remind_id+"' ");
			System.out.println("修改消息===="+sb.toString());
			int sum = genericDao.updateBySqlStr(sb.toString()); 
			System.out.println("修改的数据条数==="+sum);
			if(sum>0){
				HashMap map = new HashMap();
				map.put("ID", remind_id);
				list.add(map);
			}else{
				HashMap map = new HashMap();
				map.put("ID", "0");
				list.add(map);	
			}
		}else{
			sb.setLength(0);
			sb.append("insert into  t_calendar_warn(");
			sb.append("TITLE,");
			sb.append("START_TIME,");
			sb.append("END_TIME,");
			sb.append("NOTE,");
			sb.append("TYPE,");
			sb.append("USER_ID,");
			sb.append("REMIND_TIME,");
			sb.append("STATUS");
			sb.append(")values(");
			sb.append("'"+title+"',");
			sb.append("'"+startTime+"',");
			sb.append("'"+endTime+"',");
			sb.append("'"+note+"',");
			sb.append("'"+type+"',");
			sb.append("'"+userId+"',");
			sb.append("'"+remindTimt+"',");
			sb.append("'0'");
			sb.append(")");
			int sum = genericDao.updateBySqlStr(sb.toString());
			System.out.println("保存的数据条数==="+sum);
			
			//查询当前的主键
			sb.setLength(0);
			sb.append("select max(ID) ID from t_calendar_warn  WHERE  USER_ID = '"+userId+"' ");
			list = genericDao.executeSql(sb.toString());
		}

		return list;
	}
	
	public List  updateRemind(String id,String userId) {

		StringBuffer sb = new StringBuffer();
		sb.append("update  t_calendar_warn  set STATUS='2' where ID = '"+id+"' and user_id= '"+userId+"'");
		System.out.println("修改消息提醒==="+sb.toString());
		int sum = genericDao.updateBySqlStr(sb.toString());
		System.out.println("修改的数据条数==="+sum);
		List list = new ArrayList();
		HashMap map = new HashMap();
		map.put("result", sum);
		list.add(map);
		
		return list;
		
	}
	
	//查询频道下的栏目
	//只查常态栏目，常态栏目(0)/外送栏目(4)/内参栏目(5)/频道特别栏目(6)特别栏目(1)/栏间广告栏目(2)/导视栏目(3)
	//栏目状态(1：正常播出；2：暂停播出；3：停播)
	public List  getSubjList(String chanlCode) {
		StringBuffer sb = new StringBuffer();

		sb.append(" SELECT Z_SUBJ_CD,Z_SUBJ_NAME FROM  zcdm_t_subj  WHERE  z_chanl_cd='"+chanlCode+"' AND  z_subj_type='0' AND z_subj_status='0'");
		System.out.println("查询频道下==="+sb.toString());
		List  list = genericDao.executeSql(sb.toString());

		return list;
	}
	
	
	/**
	 * 
	 * @ps:根据栏目获取节目单子表的部分内容,
	 * @return
	 * 只查询未入库节目
	 */
	public List<Map<String, ?>> getSubjProlist(String subjCode,String userId) {
		StringBuffer sb = new StringBuffer();
		
		sb.append("SELECT a.z_procode,");
		sb.append("a.z_aud_ver,");
		sb.append("a.z_cap_ver,");
		sb.append("a.z_subj_cd,");
		sb.append("a.z_pgnam,");
		sb.append("a.z_time_estm,");
		sb.append("a.z_prodstat ");
		sb.append(" FROM zppm_t_proginfo a ");
		sb.append(" WHERE NOT EXISTS (SELECT f_procode ");
		sb.append(" FROM t_myconcern b ");
		sb.append(" WHERE b.f_procode = a.z_procode ");
		sb.append(" AND b.f_user = '"+userId+"' ");
		sb.append(" AND b.f_state = '0') ");
		sb.append(" AND a.z_subj_cd = '"+subjCode+"' ");
		sb.append(" AND (a.z_Prodstat IS NULL OR ");
		sb.append(" (a.z_Prodstat IS NOT NULL AND a.z_Prodstat != '6' AND ");
		sb.append("  a.z_Prodstat != '9')) ");
		sb.append(" ORDER BY a.z_procode DESC ");


		System.out.println("查询待关注的栏目===="+sb.toString());
		List<Map<String, ?>> result = genericDao.executeSql(sb.toString());
		return result;
	}
	
}
